package com.itheima.dao;

import com.itheima.domain.Student;
import com.itheima.utils.DateUtil;
import com.itheima.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;

public class DaoStudentImpl implements DaoStudent {
    //查询全部学生信息
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con =null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try{
           /* //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db14","root","root");*/
            con=JdbcUtils.getCon();
            //3.获取执行对象

//            stat=con.createStatement();
            //使用PerpareadStatement防止sql注入问题
            String sql="select * from student";
            stat=con.prepareStatement(sql);
            //4.执行sql语句，并返回结果
            rs=stat.executeQuery();
            //5.处理结果
            while(rs.next()){
//                String birthday = new SimpleDateFormat("yyyy-MM-dd").format(rs.getDate("birthday"));


                Student stu=  new Student(rs.getInt("sid"),rs.getString("name"),rs.getInt("age"),rs.getDate("birthday"));
                list.add(stu);
            }

        }catch (Exception e){
            e.printStackTrace();

        }finally{
            //6.释放资源
            JdbcUtils.close(rs,stat,con);

        }
//        System.out.println("llllllllllllllll");
        return list;
    }


    //查询指定学生信息
    @Override
    public Student findById(Integer id) {

        Student stu = null;
        Connection con= null;
        PreparedStatement stat= null;
        ResultSet rs = null;
        try{

            /*//1.获取连接
            Class.forName("com.mysql.jdbc.Driver");
            //2.注册驱动
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db14","root","root");*/
            con=JdbcUtils.getCon();

            //3.获取执行对象
//            stat = con.createStatement();
            //4.执行sql语句并返回结果
//            String sql="select * from student where sid="+id+"";
            stat=con.prepareStatement("select * from student where sid=? ");
            stat.setInt(1,id);
            rs=stat.executeQuery();
            //5.处理结果
            if (rs.next()){
               /* SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date birthday = sdf.parse(sdf.format(rs.getDate("birthday")));*/

                stu =new Student(rs.getInt("sid"),rs.getString("name"),rs.getInt("age"),rs.getDate("birthday"));

            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {

            //6.利用工具类释放资源
            JdbcUtils.close(rs,stat,con);

        }

        return stu;
    }

    //新增学生信息
    @Override
    public int insert(Student stu) {
        int result=0;
        Connection con=null;
        Statement stat = null;


        try{

            /*//1.连接驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.注册连接
            con=DriverManager.getConnection("jdbc:mysql://localhost/db14","root","root");*/
            con=JdbcUtils.getCon();
            //3.获取执行对象
            stat=con.createStatement();
            //4.执行sql语句并返回结果集
//            String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
            String sql = "INSERT INTO student VALUES ("+stu.getSid()+",'"+stu.getName()+"',"+stu.getAge()+",'"+ DateUtil.date2Str(stu.getBirthday()) +"')";
            stat=con.createStatement();
//            String sql = "insert into student values("+stu.getSid()+",'"+stu.getName()+"',"+stu.getAge()+",'"+birthday+"')";
            result = stat.executeUpdate(sql);
            //5.处理结果



        }catch(Exception e){
            e.printStackTrace();

        }finally {
            //6.利用工具类释放资源
            JdbcUtils.close(stat,con);
        }
        return result;
    }
    //修改学生信息
    @Override
    public int update(Student stu) {
        int result=0;
        Connection con=null;
        PreparedStatement stat = null;
        try{
           /* //1.连接驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.注册连接
            con=DriverManager.getConnection("jdbc:mysql://localhost/db14","root","root");*/
            con=JdbcUtils.getCon();
            //3.获取执行对象
//            stat=  con.createStatement();
            //4.执行sql语句并返回结果集
//            String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
            String sql = "update student set sid="+stu.getSid()+",name='"+stu.getName()+"',age="+stu.getAge()+",birthday='"+DateUtil.date2Str(stu.getBirthday())+"' where sid=?";

            stat=con.prepareStatement(sql);
            stat.setInt(1,stu.getSid());
            result = stat.executeUpdate();
            //5.处理结果

        }catch(Exception e){
            e.printStackTrace();

        }finally {
            //6.利用工具类释放资源
            JdbcUtils.close(stat,con);

        }


        return result;
    }
    //删除学生信息
    @Override
    public int delete(Integer sid) {
        int result=0;
        Connection con=null;
        PreparedStatement stat = null;
        try{
           /* //1.连接驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.注册连接
            con=DriverManager.getConnection("jdbc:mysql://localhost/db14","root","root");*/
            con=JdbcUtils.getCon();
            //3.获取执行对象
//            stat=  con.createStatement();
            //4.执行sql语句并返回结果集
            String sql = "delete from student where sid=?";
            stat=con.prepareStatement(sql);
            stat.setInt(1,sid);
            result = stat.executeUpdate();
            //5.处理结果

        }catch(Exception e){
            e.printStackTrace();

        }finally {
            //6.利用工具类释放资源
            JdbcUtils.close(stat,con);
        }
        return result;
    }
}
